using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using JTest;
using JTest.DTO;
using System.Windows.Forms;


namespace JTest.DAO
{
    /// <summary>
    /// version 02102010
    /// </summary>
    public class QuestionDAO
    {

        public static bool insert(string connStr, string type, int level, QuestionDTO question)
        {
            bool success = false;
            try
            {
                using (OleDbConnection conn = DataProvider.openConnection(connStr))
                {
                    StringBuilder query = new StringBuilder();
                    query.Append("INSERT INTO " + type + "level" + level + " ");
                    query.Append("([question],[A],[B],[C],[D],[Answer]) ");
                    query.Append("VALUES('" + question.Question + "','" + question.AnswerA + "','");
                    query.Append(question.AnswerB + "','" + question.AnswerC + "','" + question.AnswerD + "','");
                    query.Append(question.Answer + "')");

                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = query.ToString();

                    int rs = cmd.ExecuteNonQuery();

                    success = (rs == 1) ? true : false;

                }
            }
            catch (OleDbException ex)
            {
                throw ex;
            }

            return success;
        }

        /// <summary>
        /// @Author: LuongGV
        /// </summary>
        /// <param name="numOfQuestions"></param>
        /// <param name="type"></param>
        /// <param name="level"></param>
        /// <returns></returns>
        public static List<QuestionDTO> getTestQuestionList(int numOfQuestions, string type, int level)
        {
            List<QuestionDTO> list = null;
            try
            {
                using (OleDbConnection conn = DataProvider.ConnectionData())
                {
                    StringBuilder query = new StringBuilder();
                    query.Append("SELECT id,question,A,B,C,D,answer ");
                    query.Append("FROM " + type + "level" + level + " ");

                    OleDbCommand myCommand = new OleDbCommand();
                    OleDbDataReader reader;
                    myCommand.Connection = conn;
                    myCommand.CommandText = query.ToString();

                    reader = myCommand.ExecuteReader();

                    list = getQuestionListFromReader(reader);
                }
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            return list;
        }

        /// <summary>
        /// @Author: LuongGV
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static QuestionDTO getQuestionFromReader(OleDbDataReader reader)
        {

            QuestionDTO q = new QuestionDTO();

            q.ID = (int)reader["id"];
            q.Question = reader["question"].ToString();
            q.AnswerA = reader["A"].ToString();
            q.AnswerB = reader["B"].ToString();
            q.AnswerC = reader["C"].ToString();
            q.AnswerD = reader["D"].ToString();
            q.Answer = reader["answer"].ToString()[0];

            return q;
        }

        /// <summary>
        /// @Author: LuongGV
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static List<QuestionDTO> getQuestionListFromReader(OleDbDataReader reader)
        {
            List<QuestionDTO> questionlist = new List<QuestionDTO>();

            while (reader.Read())
            {
                QuestionDTO question = getQuestionFromReader(reader);

                questionlist.Add(question);
            }
            return questionlist;
        }

        /// <summary>
        /// Ham lay toan bo cau hoi trong mot bang.
        /// @Author: SonLT4
        /// </summary>
        /// <param name="level">kieu int, chua cap do cau hoi</param>
        /// <param name="type">kieu string, chua loai cau hoi</param>
        /// <returns>mang cac QuestionDTO, chua noi dung cac cau hoi</returns>
        /// <seealso cref="QuestionDTO"/>
        public static List<QuestionDTO> getAllQuestionList(int level, string type)
        {
            List<QuestionDTO> list = new List<QuestionDTO>();
            try
            {
                OleDbConnection cn = DataProvider.ConnectionData();
                string tableName = type + "level" + level;
                string strSQL = "SELECT * FROM " + tableName;

                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
           
                while (dr.Read())
                {
                    QuestionDTO q = new QuestionDTO();
                    q.ID = (int)dr["id"];
                    q.Question = dr["question"].ToString();
                    q.AnswerA = dr["A"].ToString();
                    q.AnswerB = dr["B"].ToString();
                    q.AnswerC = dr["C"].ToString();
                    q.AnswerD = dr["D"].ToString();
                    q.Answer = dr["answer"].ToString()[0];
                    list.Add(q);
                }
                cn.Close();
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            return list;
        }

        /// <summary>
        /// Ham lay ve cac cau hoi trong mot khoang nao do cua bang, database theo setting cua chuong trinh
        /// @Author: SonLT4
        /// </summary>
        /// <param name="begin">kieu int, chua id cua cau bat dau</param>
        /// <param name="end">kieu int, chua id cua cau ket thuc</param>
        /// <param name="level">kieu int, chua cap do cau hoi</param>
        /// <param name="type">kieu string, chua loai cau hoi</param>
        /// <returns>kieu List<QuestionDTO>, mang cac QuestionDTO chua thong tin cau hoi</returns>
        public static List<QuestionDTO> getQuestionListInRange(int begin, int end, int level, string type)
        {
            List<QuestionDTO> list = new List<QuestionDTO>();
            try
            {
                OleDbConnection cn = DataProvider.ConnectionData();

                string tableName = type + "level" + level;
                string strSQL = "SELECT * FROM " + tableName + " ";
                strSQL += "WHERE id BETWEEN " + begin + " and " + end;

                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                OleDbDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    QuestionDTO q = new QuestionDTO();
                    q.ID = (int)dr["id"];
                    q.Question = dr["question"].ToString();
                    q.AnswerA = dr["A"].ToString();
                    q.AnswerB = dr["B"].ToString();
                    q.AnswerC = dr["C"].ToString();
                    q.AnswerD = dr["D"].ToString();
                    q.Answer = dr["answer"].ToString()[0];
                    list.Add(q);
                }
                cn.Close();
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            return list;
        }

        /// <summary>
        /// Ham lay ve mot cau hoi trong bang CSDL theo id cua cau hoi do
        /// @Author: SonLT4
        /// </summary>
        /// <param name="id">kieu int, chua id cau hoi</param>
        /// <param name="level">kieu int, chua cap do cau hoi</param>
        /// <param name="type">kieu string, chua </param>
        /// <returns>kieu QuestionDTO, chua thong tin cau hoi</returns>
        public static QuestionDTO getQuestionByID(int id, int level, string type)
        {
            QuestionDTO q = new QuestionDTO();
            try
            {
                OleDbConnection cn = DataProvider.ConnectionData();

                string tableName = type + "level" + level;
                string strSQL = "SELECT * FROM " + tableName + " ";
                strSQL += "WHERE id = " + id;

                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                OleDbDataReader dr = cmd.ExecuteReader();

               
                if (dr.Read())
                {
                    q.ID = (int)dr["id"];
                    q.Question = dr["question"].ToString();
                    q.AnswerA = dr["A"].ToString();
                    q.AnswerB = dr["B"].ToString();
                    q.AnswerC = dr["C"].ToString();
                    q.AnswerD = dr["D"].ToString();
                    q.Answer = dr["answer"].ToString()[0];

                }
                else//khong tim thay question co id nhu yeu cau
                {
                    dr.Close();
                    cn.Close();
                    return null;
                }
                dr.Close();
                cn.Close();
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            return q;
        }
        /// <summary>
        /// Get all data from database
        /// </summary>
        /// <param name="strTableName">strTableName</param>
        /// <returns>List QuestionDTO</returns>
        public List<QuestionDTO> GetAll(string strTableName)
        {
            //create string connection
            OleDbConnection cnn = null;
            try
            {
                cnn = DataProvider.ConnectionData();
                List<QuestionDTO> List = new List<QuestionDTO>();

                // create string Query
                string strSQL = "Select * from " + strTableName;
                OleDbCommand Cmd = new OleDbCommand(strSQL, cnn);
                OleDbDataReader Reader = Cmd.ExecuteReader();

                while (Reader.Read())
                {
                    //Entity: save all entity QuestionDTO from Reader to List<QuestionDTO>
                    QuestionDTO q = new QuestionDTO();
                    q.ID = (int)Reader["ID"];
                    q.Question = (string)Reader["Question"];
                    q.AnswerA = (string)Reader["A"];
                    q.AnswerB = (string)Reader["B"];
                    q.AnswerC = (string)Reader["C"];
                    q.AnswerD = (string)Reader["D"];
                    q.Answer = Reader["answer"].ToString()[0];

                    List.Add(q);
                }

                return List;

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cnn != null)
                    cnn.Close();
            }

        }

        /// <summary>
        /// Ham lay mang cac cau hoi ngau nhien trong mot khoang
        /// @Author: SonLT4
        /// </summary>
        /// <param name="begin">kieu int, chua id cau hoi bat dau</param>
        /// <param name="end">kieu int, chua id cau hoi ket thuc</param>
        /// <param name="type">kieu string, chua loai cau hoi</param>
        /// <param name="level">kieu int, chua cap do cau hoi</param>
        /// <returns>mang cac QuestionDTO</returns>
        public static List<QuestionDTO> getRandomQuestionInRange(int begin, int end, string type, int level)
        {
            List<QuestionDTO> list = new List<QuestionDTO>();
            try
            {
                OleDbConnection cn = DataProvider.ConnectionData();
                string tableName = type + "level" + level;
                string strSQL = "SELECT * FROM " + tableName + " " +
                                "WHERE id BETWEEN " + begin + " and " + end;// +" " +
                //"ORDER BY rnd(id)";

                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
               
                while (dr.Read())
                {
                    QuestionDTO q = new QuestionDTO();
                    q.ID = (int)dr["id"];
                    q.Question = dr["question"].ToString();
                    q.AnswerA = dr["A"].ToString();
                    q.AnswerB = dr["B"].ToString();
                    q.AnswerC = dr["C"].ToString();
                    q.AnswerD = dr["D"].ToString();
                    q.Answer = dr["answer"].ToString()[0];

                    list.Add(q);
                }
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            return list;
        }

        /// <summary>
        /// Ham lay so row hien co cua table
        /// @Author: TanLA
        /// </summary>
        /// <param name="table">ten table muon lay so row</param>
        /// <returns></returns>
        public int getMaxID(string table)
        {
            int maxId = 0;
            int row = 0;
            OleDbConnection cn = DataProvider.ConnectionData();
            string query = "Select count(*) from " + table;
            try
            {
                OleDbCommand cmd = new OleDbCommand(query, cn);
                row = (int)(cmd.ExecuteScalar());
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        maxId = (int)dr[0];
                    }
                    dr.Close();
                }
                if (row != 0)
                    return maxId;
                else return row;
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            finally
            {
                if (cn != null)
                    cn.Close();
            }

        }

        /// <summary>
        /// @Author: SonLT4
        /// Ham lay ve mang cac danh sach theo cac ids cho truoc, datatbase theo setting cua chuong trinh
        /// </summary>
        /// <param name="ids">kieu int[], chua list cac ids</param>
        /// <param name="level">kieu int, chua level cau hoi</param>
        /// <param name="type">kieu string, chua loai cau hoi</param>
        /// <returns>kieu List<QuestionDTO>,chua thong tin cau hoi</returns>
        public static List<QuestionDTO> getQuestionsFromIDsList(int[] ids, int level, string type)
        {
            List<QuestionDTO> l = new List<QuestionDTO>();
            try
            {
                string temp = "(";
                for (int i = 0; i < ids.Length - 1; i++)
                {
                    temp += ids[i] + " , ";
                }
                temp += ids[ids.Length - 1] + ")";
                OleDbConnection cn = DataProvider.ConnectionData();

                string tableName = type + "level" + level;
                string strSQL = "SELECT * FROM " + tableName + " " +
                                "WHERE id IN " + temp;

                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
                int intRandom = -1;
                Random random = new Random();
                int intRightAns = 0;
                while (dr.Read())
                {
                    QuestionDTO q = new QuestionDTO();
                    q.ID = (int)dr["id"];
                    q.Question = dr["question"].ToString();
                    q.AnswerA = dr["A"].ToString();
                    q.AnswerB = dr["B"].ToString();
                    q.AnswerC = dr["C"].ToString();
                    q.AnswerD = dr["D"].ToString();
                    q.Answer = dr["answer"].ToString()[0];

                    l.Add(q);
                }
                cn.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            return l;
        }

        /// <summary>
        /// Ham lay ve mang cau hoi theo cac ids cho truoc, tu database luu trong SettingPractice
        /// </summary>
        /// <param name="ids">mang cac ids, kieu int[]</param>
        /// <param name="sp">kieu SettingPractice, chua thong tin luyen tap</param>
        /// <returns></returns>
        public static List<QuestionDTO> getQuestionsFromIDsList(int[] ids, int level, string type, OleDbConnection connection)
        {
            List<QuestionDTO> l = new List<QuestionDTO>();
            try
            {
                string temp = "(";
                for (int i = 0; i < ids.Length - 1; i++)
                {
                    temp += ids[i] + " , ";
                }
                temp += ids[ids.Length - 1] + ")";
                OleDbConnection cn = connection;

                string tableName = type + "level" + level;
                string strSQL = "SELECT * FROM " + tableName + " " +
                                "WHERE id IN " + temp;

                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
                int intRandom = -1;
                Random random = new Random();
                int intRightAns = 0;
                while (dr.Read())
                {
                    QuestionDTO q = new QuestionDTO();
                    q.ID = (int)dr["id"];
                    q.Question = dr["question"].ToString();
                    q.AnswerA = dr["A"].ToString();
                    q.AnswerB = dr["B"].ToString();
                    q.AnswerC = dr["C"].ToString();
                    q.AnswerD = dr["D"].ToString();
                    q.Answer = dr["answer"].ToString()[0];

                    l.Add(q);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return l;
        }

        /// <summary>
        /// Ham lay ve danh sach cac cau hoi trong mot khoang cua bang, theo database luu trong SettingPractice
        /// </summary>
        /// <param name="sp">kieu SettingPracticeDTO, chua thong tin luyen tap</param>
        /// <returns>mang cac QuestionDTO</returns>
        public static List<QuestionDTO> getQuestionListInRange(int begin, int end, int level, string type, OleDbConnection connection)
        {
            List<QuestionDTO> list = new List<QuestionDTO>();
            try
            {
                OleDbConnection cn = connection;

                string tableName = type + "level" + level;
                string strSQL = "SELECT * FROM " + tableName + " ";
                strSQL += "WHERE id BETWEEN " + begin + " and " + end;

                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
                int intRandom = -1;
                Random random = new Random();
                int intRightAns = 0;
                while (dr.Read())
                {
                    QuestionDTO q = new QuestionDTO();
                    q.ID = (int)dr["id"];
                    q.Question = dr["question"].ToString();
                    q.AnswerA = dr["A"].ToString();
                    q.AnswerB = dr["B"].ToString();
                    q.AnswerC = dr["C"].ToString();
                    q.AnswerD = dr["D"].ToString();
                    q.Answer = dr["answer"].ToString()[0];

                    list.Add(q);
                }
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            return list;
        }

        //    #region<Dao vi tri cau hoi>
        //// ToanNN
        //if (q.Answer == 'A')
        //{
        //    intRightAns = 1;
        //}
        //else if (q.Answer == 'B')
        //{
        //    intRightAns = 2;
        //}
        //else if (q.Answer == 'C')
        //{
        //    intRightAns = 3;
        //}
        //else
        //{
        //    intRightAns = 4;
        //}
        //intRandom = random.Next(5);
        //string a = q.AnswerA;
        //string b = q.AnswerB;
        //string c = q.AnswerC;
        //string d = q.AnswerD;

        //if (q.Answer == 'A')
        //{
        //    if (intRandom == 2)
        //    {
        //        command.SwapString(ref a, ref b);
        //        q.Answer = 'B';
        //    }
        //    else if (intRandom == 3)
        //    {
        //        command.SwapString(ref a, ref c);
        //        q.Answer = 'C';
        //    }
        //    else if (intRandom == 4)
        //    {
        //        command.SwapString(ref a, ref d);
        //        q.Answer = 'D';
        //    }
        //}
        //else if (q.Answer == 'B')
        //{
        //    if (intRandom == 1)
        //    {
        //        command.SwapString(ref a, ref b);
        //        q.Answer = 'A';
        //    }
        //    else if (intRandom == 3)
        //    {
        //        command.SwapString(ref b, ref c);
        //        q.Answer = 'C';
        //    }
        //    else if (intRandom == 4)
        //    {
        //        command.SwapString(ref b, ref d);
        //        q.Answer = 'D';
        //    }
        //}
        //else if (q.Answer == 'C')
        //{
        //    if (intRandom == 1)
        //    {
        //        command.SwapString(ref a, ref c);
        //        q.Answer = 'A';
        //    }
        //    else if (intRandom == 2)
        //    {
        //        command.SwapString(ref b, ref c);
        //        q.Answer = 'B';
        //    }
        //    else if (intRandom == 4)
        //    {
        //        command.SwapString(ref c, ref d);
        //        q.Answer = 'D';
        //    }
        //}
        //else if (q.Answer == 'D')
        //{
        //    if (intRandom == 2)
        //    {
        //        command.SwapString(ref d, ref b);
        //        q.Answer = 'B';
        //    }
        //    else if (intRandom == 3)
        //    {
        //        command.SwapString(ref d, ref c);
        //        q.Answer = 'C';
        //    }
        //    else if (intRandom == 1)
        //    {
        //        command.SwapString(ref a, ref d);
        //        q.Answer = 'A';
        //    }
        //}

        //q.AnswerA = a;
        //q.AnswerB = b;
        //q.AnswerC = c;
        //q.AnswerD = d;
        //#endregion<Doi vi tri cau hoi>
    }
}
